/**
 * 
 */
package org.rk.core.tools.office.util.excelUtil;

import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.Set;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.commons.io.FileUtils;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.rk.core.common.util.RKAlert;
import org.rk.core.common.util.RKAppConfig;
import org.rk.core.common.util.RKDateUtil;
import org.rk.core.common.util.RkObjectUtil;
import org.rk.core.common.util.RkStrUtil;
import org.rk.core.common.util.dateUtil.RKDateStyle;
import org.rk.core.common.util.file.DownloadUtils;
import org.rk.core.tools.office.mapping.ToolOfficeExcelMapping;
import org.rk.core.tools.office.mapping.service.IToolOfficeExcelMappingService;
import org.rk.core.tools.office.util.OfficeConst;
import org.springframework.web.multipart.MultipartFile;

/**
 * @author：曹仁道
 * @描述：excel导入工具类 
 * 2016年10月14日 下午5:45:04
 */
public class ExcelUtil {
	private static int titleRowNum=-1;
	private static int headRowNum=-1;
	private static int contentStarIndex=-1;
	
	private static IToolOfficeExcelMappingService toolOfficeExcelMappingService=RKAppConfig.getBean("ToolOfficeExcelMappingService");
	
	/**
	 * @方法描述 读取excel文件，返回sheetBean对象
	 * @param is excel文件流
	 * @param tRowNum 标题所在行号
	 * @param hRowNum 表头所在行号
	 * @return
	 * @throws IOException
	 */
	@SuppressWarnings({ "resource" })
	public static List<SheetBean> readExcel(InputStream is,int tRowNum,int hRowNum) throws IOException {
		List<SheetBean> sheetResultList=new ArrayList<SheetBean>();
		XSSFWorkbook xssfWorkbook = new XSSFWorkbook(is);
		// 循环工作表Sheet
		for (int numSheet = 0; numSheet < xssfWorkbook.getNumberOfSheets(); numSheet++) {
			XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(numSheet);
			if (xssfSheet == null) {
				continue;
			}
			SheetBean sheetBean = new SheetBean();
			sheetBean.setSheetName(xssfSheet.getSheetName());
			sheetBean.setTitleRowNum(tRowNum-1);
			sheetBean.setHeadRowNum(hRowNum-1);
			
			List<String> headList = new ArrayList<String>();
			List<Map<String, Object>> contentList = new ArrayList<Map<String, Object>>();
			// 循环行Row
			for (int rowNum = 0; rowNum <= xssfSheet.getLastRowNum(); rowNum++) {
				XSSFRow xssfRow = xssfSheet.getRow(rowNum);
				if (xssfRow == null) {
					continue;
				}
				// 标题
				if (sheetBean.getTitleRowNum() == rowNum) {
					sheetBean.setTitle(xssfRow.getCell(0).toString());
					continue;
				}
				// 表头
				if (sheetBean.getHeadRowNum() == rowNum) {
					// 循环列Cell
					for (int headCellNum = 0; headCellNum <= xssfRow.getLastCellNum(); headCellNum++) {
						XSSFCell cell = xssfRow.getCell(headCellNum);
						if (cell == null) {
							continue;
						}
						if(getValue(cell)==null){
							continue;
						}
						headList.add(String.valueOf(getValue(cell)));
					}
					sheetBean.setHeadList(headList);
					continue;
				}
				Map<String, Object> map = new HashMap<String, Object>();
				// 循环列Cell
				for (int cellNum = 0; cellNum <= xssfRow.getLastCellNum(); cellNum++) {
					XSSFCell cell = xssfRow.getCell(cellNum);
					if (cell == null) {
						continue;
					}
					Object cellVlaue=getValue(cell);
					if(cellVlaue==null||cellVlaue.equals("")){
						continue;
					}
					map.put(headList.get(cellNum), getValue(cell));
				}
				contentList.add(map);
				sheetBean.setContents(contentList);
			}
			sheetResultList.add(sheetBean);
		}
		return sheetResultList;
	}
	/**
	 * @方法描述 读取excel文件，返回sheetBean对象，标题行不存在
	 * @param is
	 * @param hRowNum 表头行号
	 * @return
	 * @throws IOException
	 */
	public static List<SheetBean> readExcel(InputStream is,int hRowNum) throws IOException {
		return readExcel(is, 0, hRowNum);
	}
	/**
	 * @方法描述 读取excel文件，返回sheetBean对象，标题行不存在,表头行号默认第一行
	 * @param is
	 * @return
	 * @throws IOException
	 */
	public static List<SheetBean> readExcel(InputStream is) throws IOException {
		return readExcel(is, 0, 1);
	}
	/**
	 * 
	 * @author：曹仁道
	 * @描述：读取excel中的内容
	 * @param xssfCell
	 * @return
	 * 2016年10月15日 下午12:49:37
	 */
	@SuppressWarnings("deprecation")
	private static Object getValue(XSSFCell xssfCell) {
		if (xssfCell.getCellType()== XSSFCell.CELL_TYPE_BOOLEAN) {
			// 返回布尔类型的值
			return xssfCell.getBooleanCellValue();
		} else if (xssfCell.getCellType() == XSSFCell.CELL_TYPE_NUMERIC) {
			// 返回数值类型的值
			return xssfCell.getNumericCellValue();
		//	long dd=(long)xssfCell.getNumericCellValue();
		//	return dd+"";
		} else {
			// 返回字符串类型的值
			return xssfCell.getStringCellValue();
		}
	}
	/**
	 * 
	 * @author：曹仁道
	 * @描述：导出excel文件入口
	 * @param request
	 * @param response
	 * @param sheetBean
	 * @throws Exception
	 * 2016年10月18日 上午10:15:01
	 */
	public static String exportExcel(HttpServletRequest request, HttpServletResponse response,String title,int titleRow,int headRow,List<Map<String,Object>> datas,String opraType){
		SheetBean sheetBean=new SheetBean();
		sheetBean.setTitle(title);
		if(titleRow>0){
			titleRowNum=titleRow;
		}
		sheetBean.setTitleRowNum(titleRowNum);
		if(headRow>0){
			headRowNum=headRow;
		}else{
			headRowNum=1;
		}
		sheetBean.setHeadRowNum(headRowNum);
		List<ToolOfficeExcelMapping> mappingList=toolOfficeExcelMappingService.selectExcelMappingList(opraType,OfficeConst.excelImorex.exportExcel);
		List<String> headList=mapping2Heads(mappingList);
		List<Map<String,Object>> contents=mapping2Contents(mappingList, datas);
		sheetBean.setHeadList(headList);
		sheetBean.setContents(contents);
		String fileName=RKDateUtil.getNowStr(RKDateStyle.YYYYMMDDHHMMSS)+"#"+(int)(Math.random()*1000);
		String filePath=request.getServletContext().getRealPath("/")+fileName+".xlsx";
		OutputStream out;
		try {
			out = new FileOutputStream(filePath);
			if(!RkStrUtil.hasText(sheetBean.getSheetName())){
				sheetBean.setSheetName(fileName);
			}
			XSSFWorkbook hwb=SheetBean2Excel(sheetBean);
			
			
			hwb.write(out);
			out.close();
		} catch (FileNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		try {
			DownloadUtils.download(request, response, filePath);
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		FileUtils.deleteQuietly(new File(filePath));
		return filePath;
	}
	public static String exportExcel(HttpServletRequest request, HttpServletResponse response,int headRow,List<Map<String,Object>> datas,String opraType){
		return exportExcel(request, response, "", 0, headRow, datas, opraType);
	}
	private static List<Map<String,Object>> mapping2Contents(List<ToolOfficeExcelMapping> mappingList,List<Map<String,Object>> datas){
		List<Map<String,Object>> contents=new ArrayList<Map<String,Object>>();
		for (Map<String, Object> data : datas) {
			Map<String,Object> content=new HashMap<String, Object>();
			
			for (ToolOfficeExcelMapping toolOfficeExcelMapping : mappingList) {
				String localfield=toolOfficeExcelMapping.getLocalField();
				String excelfield=toolOfficeExcelMapping.getResourceField();
				Object result=data.get(localfield);
				content.put(excelfield, result);
			}
			contents.add(content);
		}
		return contents;
	}
	private static List<String> mapping2Heads(List<ToolOfficeExcelMapping> mappingList){
		List<String> heads=new ArrayList<String>();
		for (ToolOfficeExcelMapping toolOfficeExcelMapping : mappingList) {
			String excelfield=toolOfficeExcelMapping.getResourceField();
			heads.add(excelfield);
		}
		return heads;
	}
	/**
	 * 
	 * @author：曹仁道
	 * @描述：导出excel文档
	 * @param sheetBean 表对象
	 * @throws Exception
	 * 2016年10月15日 下午12:51:33
	 */
	private static XSSFWorkbook SheetBean2Excel(SheetBean sheetBean) throws Exception {
		
		XSSFWorkbook xwb=new XSSFWorkbook();
		XSSFSheet sheet =xwb.createSheet(sheetBean.getSheetName());
		
		List<Map<String,Object>> contents=sheetBean.getContents();
		List<String> headList=sheetBean.getHeadList();
		//标题行
		if(RkStrUtil.hasText(sheetBean.getTitle())){
			if(sheetBean.getTitleRowNum()>0){
				titleRowNum=(sheetBean.getTitleRowNum()-1);
			}
			setSheetTitle(xwb,sheet,sheetBean.getTitle(),headList.size());
		}
		//表头行
		if(headList!=null&&headList.size()>0){
			setSheetHead(xwb,sheet,headList);
		}
		//正文部分
		if(contents!=null&&contents.size()>0){
			setSheetContent(xwb,sheet,contents,headList);
		}
		return xwb;
	}
	/**
	 * 
	 * @author：曹仁道
	 * @描述：设置标题行
	 * @param xwb
	 * @param sheet
	 * @param title
	 * @param cellSize
	 * 2016年10月15日 下午12:48:47
	 */
	@SuppressWarnings("deprecation")
	private static void setSheetTitle(XSSFWorkbook xwb,XSSFSheet sheet,String title,int cellSize){
		if(RkObjectUtil.isEmpty(cellSize)||cellSize<1){
			RKAlert.Error("至少一列数据");
		}
		XSSFCellStyle titleStyle=xwb.createCellStyle();
		titleStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);//垂直居中
		titleStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);//水平居中
		titleStyle.setFillBackgroundColor(IndexedColors.GREY_40_PERCENT.index);
		//设置字号
		XSSFFont font=xwb.createFont();
		font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);//加粗
		font.setFontHeightInPoints((short) 16);//字号
		titleStyle.setFont(font);
		if(titleRowNum<0){
			titleRowNum=0;
		}
		XSSFRow titleRow=sheet.createRow(titleRowNum);
		XSSFCell titleCell=titleRow.createCell(0);
		titleCell.setCellStyle(titleStyle);
		titleCell.setCellValue(title);
		sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, cellSize));
	}
	/**
	 * 
	 * @author：曹仁道
	 * @描述：设置表头行
	 * @param hwb
	 * @param sheet
	 * @param headList
	 * 2016年10月15日 下午12:48:58
	 */
	@SuppressWarnings("deprecation")
	private static void setSheetHead(XSSFWorkbook xwb,XSSFSheet sheet,List<String> headList){
		XSSFCellStyle headStyle=xwb.createCellStyle();
		headStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);//垂直居中
		headStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);//水平居中
		headStyle.setFillBackgroundColor(IndexedColors.GREY_50_PERCENT.index);
		
		//设置字号
		XSSFFont font=xwb.createFont();
		font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);//加粗
		font.setFontHeightInPoints((short) 12);//字号
		headStyle.setFont(font);
		
		headRowNum=titleRowNum+1;
		XSSFRow headRow=sheet.createRow(headRowNum);
		for(int cellNum=0;cellNum<headList.size();cellNum++){
			XSSFCell headCell=headRow.createCell(cellNum);
			headCell.setCellStyle(headStyle);
			headCell.setCellValue(headList.get(cellNum));
		}
	}
	/**
	 * 
	 * @author：曹仁道
	 * @描述：设置sheet表中的内容
	 * @param sheet
	 * @param contents
	 * 2016年10月18日 上午10:14:32
	 */
	@SuppressWarnings("deprecation")
	private static void setSheetContent(XSSFWorkbook xwb,XSSFSheet sheet,List<Map<String,Object>> contents,List<String> headList){
		XSSFCellStyle contentStyle=xwb.createCellStyle();//设置content样式
		contentStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);//垂直居中
		contentStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);//水平居中
		
		contentStarIndex=headRowNum+1;
		for (int i=0;i<contents.size();i++) {
			Map<String, Object> content=contents.get(i);
			int index=contentStarIndex+i;
			XSSFRow conentRow=sheet.createRow(index);
			
			Set<Entry<String, Object>> entry = content.entrySet();
			Iterator<Entry<String,Object>> it = entry.iterator();
			while (it.hasNext()){
				Entry<String, Object>  me = it.next();
				String key = me.getKey();  
		        String value = String.valueOf(me.getValue());
		        if(!RkStrUtil.hasText(key)){
		        	continue;
		        }
		        //获取表头中的结果
		        for (int j=0;j<headList.size();j++) {
		        	String head=headList.get(j);
		          if(key.equals(head)){
		          	//设置宽度，根据内容自适应
		          	int width=2000;
		          	if(RkStrUtil.hasText(value)){
		          		width=value.length()*500;
		          	}
		          	int alWidth=sheet.getColumnWidth(j);
		          	if(alWidth<width){
		          		sheet.setColumnWidth(j, width);
		          	}
		          	
		          	XSSFCell contentCell=conentRow.createCell(j);
		          	contentCell.setCellValue(value);
		          	contentCell.setCellStyle(contentStyle);
		          	break;
		          }
				}
        
			}
		}
	}
	
	
	/**
	 * 
	 * @author：曹仁道
	 * @描述：根据映射字段设置实体对象的属性值
	 * @param localField
	 * @param resourceField
	 * @param content
	 * @param obj
	 * @return
	 * @throws IllegalArgumentException
	 * @throws IllegalAccessException
	 * 2016年10月18日 上午10:15:37
	 */
	private static boolean setFieldValue(String localField,Object result,Object obj) throws IllegalArgumentException, IllegalAccessException{
		boolean isAl=false;
		Field[] fields=obj.getClass().getDeclaredFields();
		//基本信息
		for (Field field : fields) {
			String fieldName=field.getName();
			Class<?> fieldType=field.getType();
			String fieldTypeName=fieldType.getTypeName();
			if(fieldName!=null&&fieldName.equals(localField)){
				field.setAccessible(true);
				//Object result=content.get(resourceField);
				//注意：需要处理是否字典维护的字段
				if(fieldTypeName.equals("long")){
					field.set(obj, (Long)result);
				}else if(fieldTypeName.equals("java.lang.Long")){
					field.set(obj, Long.parseLong(String.valueOf(result)));
				}else if(fieldTypeName.equals("java.lang.String")){
					field.set(obj, String.valueOf(result));
				}else if(fieldTypeName.equals("java.math.BigDecimal")){
					if(result instanceof Double){
						field.set(obj, new BigDecimal((Double)result));
					}else{
						if(result!=null&&RkStrUtil.hasText(String.valueOf(result))&&!"null".equals(String.valueOf(result))){
							field.set(obj, new BigDecimal(String.valueOf(result)));
						}
					}
				}else{
					field.set(obj, result);
				}
				isAl=true;
			}
			if(isAl){
				break;
			}
		}
		return isAl;
	}
	/**
	 * 
	 * @方法描述：根据映射结果对实体属性赋值或读取复合字段结果V2
	 * @author:曹仁道
	 * 2016年10月28日 下午3:36:09
	 * @param mappingList
	 * @param content
	 * @param objList
	 * @param complexMap
	 * @throws IllegalArgumentException
	 * @throws IllegalAccessException
	 */
	public static void getMappinData(List<ToolOfficeExcelMapping> mappingList,Map<String, Object> content,List<Map<String, Object>> objMapList,Map<String,Object> complexMap) throws IllegalArgumentException, IllegalAccessException{
		for (ToolOfficeExcelMapping mapping : mappingList) {
			String localField=mapping.getLocalField();
			String resourceField=mapping.getResourceField();
			String tableName=mapping.getTableName();
			String beanName=mapping.getBeanName();
			String imOrEx=mapping.getImOrEx();
			//String opraType=(String) mapping.get("opraType");
			Boolean isMust=mapping.getIsMust()=="1"?true:false;
			//如果映射字段为空或配置类型为导入（2）
			if(!RkStrUtil.hasText(imOrEx)||imOrEx.equals("2")||!RkStrUtil.hasText(resourceField)){//导入映射资源字段没有不需要导入使用
				continue;
			}
			Object result=null;
			if(isMust){
				result=content.get(resourceField);
				if(result==null){
					RKAlert.Error(resourceField+"项不可为空，请检查。");
					return;
				}
			}
			//读取字段中重复出现的，进行转换成正常字段
			String times="0";
			if(localField.indexOf("#")>0){//如果出现#号表示该字段重复使用
				String[] localFieldInfo=localField.split("#");
				localField=localFieldInfo[0];
				times=localFieldInfo[1];//记录出现次数，创建实体时对应
			}
			String objMapKey=genObjMapKey(beanName,tableName,times);
			if(!RkStrUtil.hasText(objMapKey)){
				continue;
			}
			//复合类字段
			//if(Rk.EXPORTOPRATYPE.COMPLEX.equals(opraType)){
				//complexMap.put(localField, content.get(resourceField));
				//continue;
			//}
			boolean isAl=false;
			for (Map<String, Object> objMap : objMapList) {
				if(!isAl){
					if(objMapKey.equals(objMap.get("beanVariable"))){
						isAl=setFieldValue(localField,result,objMap.get("beanObject"));
					}
					if(isAl){
						break;
					}
				}else{
					break;
				}
			}
		}
	}
	public static String genObjMapKey(String beanName,String tableName,String times){
		//生成objMapKey
		String objMapKey="";
		if(!RkStrUtil.hasText(beanName)&&!RkStrUtil.hasText(tableName)){
			return "";
		}else{
			objMapKey=RkStrUtil.toLowerCaseFirst(beanName.substring((beanName.lastIndexOf(".")+1), beanName.length()));//实体bean的首字母转换成小写(取最后一个.之后的类名)
			if(!RkStrUtil.hasText(objMapKey)){//如果没有的话，根据表名转换
				String[] tmp=RkStrUtil.toUpCaseFirst(tableName.split("_"));
				objMapKey=RkStrUtil.toLowerCaseFirst(RkStrUtil.strArrToStr(tmp, ""));
			}
			if(!RkStrUtil.hasText(objMapKey)){//如果都是空的，结束映射实体
				return "";
			}
		}
		//最后将变量结果添加次数标识
		objMapKey+="#"+times;
		return objMapKey;
	}
	
	/**
	 * @方法描述：
	 * @author:曹仁道
	 * 2016年11月30日 下午2:45:12
	 */
	public static List<Map<String, Object>> importExcel(MultipartFile file,String opraType,String userName){
		List<ToolOfficeExcelMapping> mappingList=toolOfficeExcelMappingService.selectExcelMappingList(opraType,OfficeConst.excelImorex.importExcel);
		//初始化bean
		List<Map<String,Object>> objMapList=new ArrayList<Map<String,Object>>();
		objMapList=BeanMappingUtil.initBean(mappingList, objMapList, userName);
		
		Map<String,Object> complexMap=new HashMap<String, Object>();
		
		List<Map<String, Object>> contentList=new ArrayList<Map<String,Object>>();
		try {
			List<SheetBean> result = ExcelUtil.readExcel(file.getInputStream(), 1);
			for (SheetBean sheetBean : result) {
				contentList = sheetBean.getContents();
				if (contentList == null || contentList.size() < 1) {
					continue;
				}
				//先只取一个吧
				break;
			}
			//设置实体值
			objMapList=setContetToBean(mappingList, contentList, objMapList, complexMap);
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			RKAlert.Error("导入发生错误，请使用2007版以后的excel");
		}
		return objMapList;
	}
	private static List<Map<String,Object>> setContetToBean(List<ToolOfficeExcelMapping> mappingList,List<Map<String, Object>> contentList,List<Map<String,Object>> objMapList,Map<String,Object> complexMap){
		try {
			for (Map<String, Object> content : contentList) {
				getMappinData(mappingList, content, objMapList, complexMap);
			}
			return objMapList;
		} catch (IllegalArgumentException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}
}
